﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.ApplicationBlocks.Data;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace EnglishRemix.Services.Utility
{
    public class DBUtility
    {
        public static void AddSqlParam(List<SqlParameter> sqlParameterList, string parameterName, SqlDbType type, object value)
        {
            SqlParameter param = new SqlParameter();
            param.ParameterName = parameterName;
            param.SqlDbType = type;
            if (type == SqlDbType.NVarChar)
            {
                value = Convert.ToString(value).Replace("'", "''");
            }
            param.Value = value;

            sqlParameterList.Add(param);
        }

        public static DataSet ExecuteDataset(string SqlStr, List<SqlParameter> sqlParameterList)
        {
            SqlParameter[] sqlParameters = sqlParameterList.ToArray();
            string SqlConStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
            return SqlHelper.ExecuteDataset(SqlConStr, CommandType.Text, SqlStr, sqlParameters);
        }

        public static Int32 ExecuteNonQuery(string SqlStr, List<SqlParameter> sqlParameterList)
        {
            SqlParameter[] sqlParameters = sqlParameterList.ToArray();
            string SqlConStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
            return SqlHelper.ExecuteNonQuery(SqlConStr, CommandType.Text, SqlStr, sqlParameters);
        }

        public static DataSet ExecuteDataset(string SqlStr, SqlParameter[] sqlParameters)
        {
            string SqlConStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
            return SqlHelper.ExecuteDataset(SqlConStr, CommandType.Text, SqlStr, sqlParameters);
        }

        public static Int32 ExecuteNonQuery(string SqlStr, SqlParameter[] sqlParameters)
        {
            string SqlConStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
            return SqlHelper.ExecuteNonQuery(SqlConStr, CommandType.Text, SqlStr, sqlParameters);
        }
        //Add method to call stored procedure
        /// <summary>
        /// Return dataset and call Stored Procedure 
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="spParameterList"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataset(string spName, CommandType type, List<SqlParameter> spParameterList)
        {
            SqlParameter[] spParameters = spParameterList.ToArray();
            string SqlConStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
            return SqlHelper.ExecuteDataset(SqlConStr, type, spName, spParameters);
        }
        /// <summary>
        /// CRUD operation calling stored procedure
        /// </summary>
        /// <param name="SqlStr"></param>
        /// <param name="sqlParameterList"></param>
        /// <returns></returns>
        public static Int32 ExecuteNonQuery(string spName, CommandType type, List<SqlParameter> spParameterList)
        {
            SqlParameter[] spParameters = spParameterList.ToArray();
            string SqlConStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
            return SqlHelper.ExecuteNonQuery(SqlConStr, type, spName, spParameters);
        }

        public static void ExcuteBatchNonQuery(List<string> sqlCommandList, List<SqlParameter[]> sqlParameterList)
        {
            if (sqlCommandList.Count <= 0 || sqlParameterList.Count <= 0 || sqlCommandList.Count != sqlParameterList.Count)
            {
                throw new ArgumentException("Argument sqlCommandList and sqlParameterList are needed Or the number of sqlCommandList and sqlParameterList are not matched.");
            }

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString);
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                for (int i = 0; i < sqlCommandList.Count; i++)
                {
                    SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlCommandList[i], sqlParameterList[i]);
                }
                trans.Commit();
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new ApplicationException(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public static int ExecuteBatchNonQuery(List<string> sqlCommandList, List<SqlParameter> sqlParameterList)
        {
            if (sqlCommandList.Count <= 0 || sqlParameterList.Count <= 0)// || sqlCommandList.Count != sqlParameterList.Count)
            {
                throw new ArgumentException("Argument sqlCommandList and sqlParameterList are needed Or the number of sqlCommandList and sqlParameterList are not matched.");
            }
            SqlParameter[] sqlParameters = sqlParameterList.ToArray();
            int result = 1;
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString);
            //string SqlConStr = ConfigurationManager.ConnectionStrings["SqlConStr"].ConnectionString;
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                for (int i = 0; i < sqlCommandList.Count; i++)
                {
                    int temp = SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sqlCommandList[i], sqlParameters);
                    result = result & temp; 
                }
                trans.Commit();
                return result;
            }
            catch (Exception e)
            {
                trans.Rollback();
                throw new ApplicationException(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public static Boolean hasResult(DataSet ds)
        {
            return (ds != null && ds.Tables != null && ds.Tables.Count > 0 && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0);
        }
    }
}
